<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class M_student extends MY_Model
{
	/* 	
		-------------------------------------------------------------------
		get_student
		12-11-12
		perry
		-------------------------------------------------------------------
		@param $profile_id
		Gets the student's profile info
		
	*/
	function get_student($profile_id){
		$sq= 'SELECT enrollments.e_id as enrollment_id,
					 profiles.*,
					 concat(employees.first_name," ",employees.last_name) as teacher_name,
					 enrollments.e_student_id as idno,
					 DATE_FORMAT(enrollments.e_created_at,"%M %e, %Y") as date_registered,
					 DATE_FORMAT(profiles.dob,"%M %e, %Y") as dob_formated,
					 concat(profiles.firstname," ",profiles.middlename," ",profiles.lastname) as fullname,
					 enrollments.e_grade_level as level_id,
					 enrollments.e_enrollee_type as student_type,
					 enrollments.e_section_id,
					 enrollments.a_f_description,
					 enrollments.a_f_price,
					 enrollments.schedule_for_testing,
					 levels.level_code,
					 levels.level_desc,
					 levels.level_category,
					 sections.section_name,
					 bs.block_name,
					 enrollments.block,
					 enrollments.e_sy_from,
					 enrollments.e_sy_to
			  FROM enrollments
			  LEFT JOIN profiles ON profiles.profile_id = enrollments.e_profile_id
			  LEFT JOIN levels ON levels.level_id = enrollments.e_grade_level
			  LEFT JOIN sections ON sections.id = enrollments.e_section_id
			  LEFT JOIN employees ON sections.adviser_id = employees.emp_id
			  LEFT JOIN block_section bs ON enrollments.block = bs.id
			  WHERE enrollments.e_id = ?
			  LIMIT 1; 
			  ';
		$q = $this->db->query($sq,array($profile_id));
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	function get_deleted($profile_id)
	{
		$sq= 'SELECT enrollment_bin.e_id as enrollment_id,
					 profiles.*,
					 concat(profiles.sibling_fname1," ",profiles.sibling_lname1) as sibling1,
					 concat(profiles.sibling_fname2," ",profiles.sibling_lname2) as sibling2,
					 concat(profiles.sibling_fname3," ",profiles.sibling_lname3) as sibling3,
					 concat(profiles.sibling_fname4," ",profiles.sibling_lname4) as sibling4,
					 concat(profiles.sibling_fname5," ",profiles.sibling_lname5) as sibling5,
					 concat(profiles.sibling_fname6," ",profiles.sibling_lname6) as sibling6,
					 enrollment_bin.student_id as idno,
					 DATE_FORMAT(profiles.dob,"%M %e, %Y") as dob_formated,
					 concat(profiles.firstname," ",profiles.middlename," ",profiles.lastname) as fullname,
					 bs.block_name,
					 enrollment_bin.block,
					 enrollment_bin.reason
			  FROM enrollment_bin
			  LEFT JOIN profiles ON profiles.profile_id = enrollment_bin.p_id
			  LEFT JOIN block_section bs ON enrollment_bin.block = bs.id
			  WHERE enrollment_bin.e_id = ? 
			  ';
		$q = $this->db->query($sq,array($profile_id));
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	function get_student_user_id($profile_id){
		$q = $this->db->select('user_id')->where('profile_id', $profile_id)->get('profiles');
		return $q->num_rows() >= 1 ? $q->row()->user_id : FALSE;
	}
	
	function get_student_profile_id($id){
		$q = $this->db->select('profile_id')->where('user_id', $id)->get('profiles');
		return $q->num_rows() >= 1 ? $q->row()->profile_id : FALSE;
	}
	
	/* 	
		-------------------------------------------------------------------
		get_all_students
		12-11-12
		perry
		-------------------------------------------------------------------
		Gets all the students, along with the room assignment 
		
		Returns true if add success
		Returns false if add unsuccessful
	*/
	function get_all_students($data)
	{
		$query = 'SELECT
					levels.level_code,
					levels.level_desc,
					profiles.profile_id,
					enrollments.e_student_id as idno,
					enrollments.e_grade_level as level_id,
					profiles.user_id,
					profiles.firstname,
					profiles.lastname,
					profiles.middlename,
					profiles.gender
					FROM
					profiles
					LEFT JOIN enrollments ON profiles.profile_id = enrollments.e_profile_id
					LEFT JOIN levels ON enrollments.e_grade_level = levels.level_id
					WHERE enrollments.e_registered = ?
					AND enrollments.e_ay_id = ?
					AND enrollments.e_archived = 0
				';
		$q = $this->db->query($query,array($data,$this->c_sy->id));
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	/* 	
		-------------------------------------------------------------------
		update_profile
		12-11-12
		perry
		-------------------------------------------------------------------
		@param $profile_data - array containing the student's profile data
		@param $profile_id - student's profile id
		
		Updates the students profile
		
		Returns true if update success
		Returns false if update unsuccessful
	*/
	function update_profile($profile_data, $profile_id)
	{
		$this->db->where('profile_id', $profile_id)->update('profiles', $profile_data);
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	/* 	
		-------------------------------------------------------------------
		search_student
		12-11-12
		perry
		-------------------------------------------------------------------
		@param $search - item to be searched: firstname, lastname, middlename or IDNO
		@param $level - student's grade level
		
		Searches for a student
		
		Returns a student if student is found
		Returns false if student is not found
	*/
	function search_student($search, $level_id,$type){
		$query = "SELECT
					levels.level_code,
					levels.level_desc,
					profiles.profile_id,
					enrollments.e_student_id as idno,
					enrollments.e_grade_level as level_id,
					profiles.user_id,
					profiles.firstname,
					profiles.lastname,
					profiles.middlename,
					profiles.gender
					FROM
					profiles
					LEFT JOIN enrollments ON profiles.profile_id = enrollments.e_profile_id
					LEFT JOIN levels ON enrollments.e_grade_level = levels.level_id
					WHERE CONCAT(profiles.firstname, profiles.lastname, profiles.middlename, profiles.idno) 
					LIKE ? 
					AND enrollments.e_grade_level = ?
					AND enrollments.e_registered = ?
					AND enrollments.e_ay_id = ?
					AND enrollments.e_archived = 0
				";
				
		$q = $this->db->query($query,array('%'.$search.'%',$level_id,$type,$this->c_sy->id));
		return $q->num_rows() >= 1 ? $q->result() : FALSE;

	}
	
	
	/* 	
		-------------------------------------------------------------------
		assign_student_room
		12-11-12
		perry
		-------------------------------------------------------------------
		@param $profile_id - student's profile id
		@param $room_id - the id of the room the student is assigned to
				
		Assigns a student to a room
		
		Returns true if assign succes
		Returns false if assign unsuccessful
	*/
	function assign_student_room($profile_id, $room_id){
		$q = $this->db->query("SELECT ( (SELECT count(room_id) FROM profiles WHERE room_id = '$room_id') + 1) > max_students AS total_max FROM rooms WHERE room_id = '$room_id'");
		if($q->row()->total_max >= 1) {
			return 'FULL';		
		}
		else{
			$this->db->where('profile_id', $profile_id)->update('profiles', array('room_id' => $room_id));
			return $this->db->affected_rows() >= 1 ? 'SUCCESS' : FALSE;
		}
		

	}
	
	
	

	
	function delete_student($profile_id){
		$this->db->where('profile_id', $profile_id)->delete('profiles');
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	
	/*
		fetch_students for pagination
	*/
	public function fetch_students($where = false,$start,$limit,$is_registered)
	{
		$sql = 'SELECT
				levels.level_code,
				levels.level_desc,
				profiles.profile_id,
				enrollments.e_student_id as idno,
				enrollments.e_grade_level as level_id,
				profiles.user_id,
				profiles.firstname,
				profiles.lastname,
				profiles.middlename,
				profiles.gender,
				enrollments.e_id as enrollment_id
				FROM
				profiles
				LEFT JOIN enrollments ON profiles.profile_id = enrollments.e_profile_id
				LEFT JOIN levels ON enrollments.e_grade_level = levels.level_id
				LEFT JOIN sections ON sections.id = enrollments.e_section_id
				LEFT JOIN rooms ON rooms.section_id = sections.id';
		
		if($where == false)
		{

			$sql .=' WHERE enrollments.e_registered = ?
					AND enrollments.e_ay_id = ?
					 AND enrollments.e_archived = 0
					 
					LIMIT '.intval($limit).','.intval($start).' ';
					
			$q = $this->db->query($sql,array($is_registered,$this->c_sy->id));
			return $q->num_rows() >= 1 ? $q->result() : FALSE;
		
		}else{
			foreach($where as $k => $v)
			{
				if(!empty($v))
				{
					if($k == 'search_itm')
					{
						$searched_string['enrollments.e_student_id'] = '%'.$v.'%'; 
						$searched_string['profiles.firstname'] = '%'.$v.'%'; 
						$searched_string['profiles.lastname'] = '%'.$v.'%'; 
						$searched_string['profiles.middlename'] = '%'.$v.'%'; 
					}else{
						$searched_string['profiles.'.$k] = $v;
					}
				}
			}
			
			if(isset($searched_string))
			{
				if(count($searched_string) > 0)
				{
					$where = implode(' like ? OR ',array_keys($searched_string)).' like ?';
					$value = array_values($searched_string);
					$value[] = $is_registered;
							$sql .=' WHERE ('.$where.') AND enrollments.e_registered = ?
							LIMIT '.intval($limit).','.intval($start).' ';
						
					$query = $this->db->query($sql,$value);
					return $query->num_rows() > 0 ? $query->result() : FALSE;
				}else{
					$this->get_all_students(1);
				}
			}else{
				return FALSE;
			}
		}
	}
	/*End of fetch Students*/
	
	public function count_fetch_students($where = false,$is_registered)
	{
		$sql = 'SELECT
				levels.level_code,
				levels.level_desc,
				profiles.profile_id,
				enrollments.e_student_id as idno,
				enrollments.e_grade_level as level_id,
				profiles.user_id,
				profiles.firstname,
				profiles.lastname,
				profiles.middlename,
				profiles.gender
				FROM
				profiles
				LEFT JOIN enrollments ON profiles.profile_id = enrollments.e_profile_id
				LEFT JOIN levels ON enrollments.e_grade_level = levels.level_id';
				
		if($where == false)
		{
			$sql .=' WHERE enrollments.e_registered = ?
					 AND enrollments.e_ay_id = ?
					 AND enrollments.e_archived = 0';
			$q = $this->db->query($sql,array($is_registered,$this->c_sy->id));
			return $q->num_rows() >= 1 ? $q->result() : FALSE;
		}else{
			foreach($where as $k => $v)
			{
				if(!empty($v))
				{
					if($k == 'search_itm')
					{
						$searched_string['enrollments.e_student_id'] = '%'.$v.'%'; 
						$searched_string['profiles.firstname'] = '%'.$v.'%'; 
						$searched_string['profiles.lastname'] = '%'.$v.'%'; 
						$searched_string['profiles.middlename'] = '%'.$v.'%'; 
					}else{
						$searched_string['profiles.'.$k] = $v;
					}
				}
			}
			
			if(isset($searched_string))
			{
				if(count($searched_string) > 0)
				{
					$where = implode(' like ? OR ',array_keys($searched_string)).' like ?';
					$value = array_values($searched_string);
					$value[] = $is_registered;
					$value[] = $this->c_sy->id;
							$sql .=' WHERE ('.$where.') AND enrollments.e_registered = ?
									AND enrollments.e_ay_id = ?
									AND enrollments.e_archived = 0';
					$query = $this->db->query($sql,$value);
					return $query->num_rows() > 0 ? $query->result() : FALSE;
				}else{
					$this->get_all_students(1);
				}
			}else{
				return FALSE;
			}
		}
	}
	
	public function get_student_remarks($enrollment_id)
	{
		$sql = "SELECT grading_period, remarks FROM adviser_remarks WHERE enrollment_id = ? GROUP BY grading_period";
		$query = $this->db->query($sql, array($enrollment_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
		
	public function get_grading_period()
	{
		return $this->c_gp;
	}
	
	public function get_grading_period_count()
	{
		$sql = "SELECT COUNT(*) AS gp_count FROM grading_periods WHERE gp_stat = 'active'";
		$query = $this->db->query($sql);
		return $query->num_rows > 0 ? $query->row() : FALSE;
	}
	
	public function get_student_details_thru_phonebook($parameter)
	{
		$sql = "SELECT fathername, father_mname, father_lname, mothername, mother_mname, mother_lname, father_contact_no, mother_contact_no, firstname, lastname, middlename
				FROM profiles
				WHERE father_contact_no LIKE '%$parameter%' 
				OR mother_contact_no LIKE '%$parameter%'
				OR fathername LIKE '%$parameter%'
				OR mothername LIKE '%$parameter%'
				OR firstname LIKE '%$parameter%'
				OR lastname LIKE '%$parameter%'
				OR middlename LIKE '%$parameter%'";
		$query = $this->db->query($sql);
		return $query->num_rows > 0 ? $query->result() : FALSE;
	}
	
	public function check_for_requirements($idno)
	{
		$result = $this->db->where('e_id',$idno)->get('enrollment_file_checklist');
		if($result->num_rows() >= 1)
		{
			return $result->row();
		}else{
			return FALSE;
		}
	}
	
	public function checklist($data,$type,$student_id='')
	{
		if($type == 'insert')
		{
			echo'insert<br>';

			$this->db->insert('enrollment_file_checklist',$data);
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		
		}elseif($type =='update')
		{
			echo'update<br>';
			$this->db->set($data)
					 ->where('student_id',$student_id)
					 ->update('enrollment_file_checklist',$data);
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}
	}
	
	public function change_student_grade_level($e_id,$option)
	{
		
		//check if current grade level
		$q1 = $this->db->select('e_grade_level')->where('e_id',$e_id)->limit(1)->get('enrollments');
		
		if($q1->num_rows() >= 1)
		{
			//if grade is still the same do nothing.
			$lid = $q1->row()->e_grade_level;
			if($lid == $option)
			{
				return (object)array('stat'=>'false','log'=>'Grade level already set for student.');
			}else
			{
				//get ci core
				$ci =& get_instance();
				
				//get current section of student and adjust the student load
				//before we remove the student from the section
				$qsec =  $this->db->select('e_section_id')->where('e_id',$e_id)->limit(1)->get('enrollments');
				if($qsec->num_rows() >= 1)
				{
					//update section and section load
					$section_id = $qsec->row()->e_section_id;
					if($section_id !== 0)
					{
						$this->db->set('e_section_id',0)->where('e_id',$e_id)->limit(1)->update('enrollments');
						$this->db->set('current_load','current_load - 1')->where('id',$section_id)->limit(1)->update('sections');
					}
				}
				
				//update section to 0 which sets student to no section
				//update the grade level to the chosen grade level
				//reset block to the current gradelevel
				$tobeset['e_section_id'] = 0;
				$tobeset['block'] = 0;
				$tobeset['e_grade_level'] = $option; 
				$this->db->set($tobeset)->where('e_id',$e_id)->update('enrollments');
				
				$r_TRUE = (object)array('stat'=>'true','log'=>'Grade Level was successfully changed.');
				$r_FALSE = (object)array('stat'=>'false','log'=>'Unable to set new grade level.');
	
				
				
				return $this->db->affected_rows() >=1 ? $r_TRUE : $r_FALSE;
			}
		}else{
			return (object)array('stat'=>'false','log'=>'Unable to check grade level. Please try again.');
		}
	}
	
	public function get_students_by_level_id($level_id)
	{
		$sql = 'SELECT concat_ws("",p.lastname," , ",p.firstname," ",p.middlename) as fullname,
					   bs.block_name as block,
					   bs.id as block_id,
					   e.e_id as enrollment_id,
					   e.e_section_id,
					   p.dob,
					   p.gender
				FROM enrollments e
				LEFT JOIN profiles p ON e.e_profile_id = profile_id
				LEFT JOIN block_section bs ON e.block = bs.id
				WHERE e.e_ay_id = ?
				AND e.e_grade_level = ?
				AND e.registrar_approved = 1
				AND e.verified = 1
				AND e.guidance_approved = 1
				ORDER BY p.lastname';
				
		$q = $this->db->query($sql,array($this->c_sy->id,$level_id));
		return $q->num_rows() >=1 ? $q->result() : FALSE;
	}
	
	public function get_students_with($ln,$type,$start,$limit)
	{
		$start = is_numeric($start)? intval($start) : 0;
		$limit = is_numeric($limit) ? intval($limit) : 0;
		
		if(strtolower($type) == 'lastname')
		{
			$sql = 'SELECT concat_ws("",p.lastname,", ",p.firstname," ",p.middlename) as fullname,
					   bs.block_name as block,
					   e.e_id as enrollment_id,
					   e.e_section_id,
					   p.dob,
					   p.gender,
					   e.e_grade_level as gl,
					   l.level_desc
				FROM enrollments e
				LEFT JOIN profiles p ON e.e_profile_id = profile_id
				LEFT JOIN block_section bs ON e.block = bs.id
				LEFT JOIN levels l ON l.level_id = e.e_grade_level
				WHERE e.e_ay_id = ?
				AND lastname like  ?
				AND e.registrar_approved = 1
				ORDER BY p.lastname,p.firstname';
		}else{
			$sql = 'SELECT concat_ws("",p.firstname,"  ",p.middlename," ",p.lastname) as fullname,
				   bs.block_name as block,
				   e.e_id as enrollment_id,
				   e.e_section_id,
				   p.dob,
				   p.gender,
				   e.e_grade_level as gl
			FROM enrollments e
			LEFT JOIN profiles p ON e.e_profile_id = profile_id
			LEFT JOIN block_section bs ON e.block = bs.id
			WHERE e.e_ay_id = ?
			AND firstname like  ?
			AND e.registrar_approved = 1
			ORDER BY p.firstname,p.lastname ';
		}
	
		$count_q = $this->db->query($sql,array($this->c_sy->id,$ln.'%'));
		$count = $count_q->num_rows() >=1 ? $count_q->num_rows() : 0;
		
		$sql .= ' LIMIT '.$limit.' , '.$start;
		$result_q = $this->db->query($sql,array($this->c_sy->id,$ln.'%'));
		$result = $result_q->num_rows() >= 1 ? $result_q->result() : FALSE;

		return (object)['count'=>$count,'result'=>$result];
	}
	
	public function get_students_for_promotional_reports($level,$gender = '')
	{
		$sql = 'SELECT concat(p.lastname," , ",p.firstname) as fullname,
					   p.present_address as address,
					   p.age
				FROM enrollments e
				LEFT JOIN levels lvl ON e.e_grade_level = lvl.level_id
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				WHERE e.verified = 1
				AND e.e_grade_level = ?
				AND p.gender = ?
				ORDER BY p.lastname';
				
		$query = $this->db->query($sql,array($level,$gender));
		
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function for_insurance()
	{
		$sql = 'SELECT concat_ws("",p.lastname,", ",p.firstname," ",p.middlename) as fullname,
					   p.present_address as address,
					   e.e_student_id as studNo,
					   p.contact_no as contact,
					   p.mother_contact_no as mcontact,
					   p.father_contact_no as fcontact,
					   p.father_office_num as foffice,
					   p.mother_office_num as moffice,
					   p.gender,
					   date_format(p.dob,"%M %d, %Y") as bdate,
					   concat_ws("",p.mother_lname,", ",p.mothername," ",p.mother_mname) as mother,
					   concat_ws("",p.father_lname,", ",p.fathername," ",p.father_mname) as father,
					   l.level_desc
				FROM enrollments e
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				LEFT JOIN levels l ON e.e_grade_level = l.level_id
				WHERE e.verified = 1
				AND e.registrar_approved = 1
				AND e.e_archived = 0
				AND e.e_ay_id = ?
				ORDER BY p.lastname';

		$res = $this->db->query($sql,[$this->c_sy->id]);
		return $res->num_rows() >=1 ? $res->result() : FALSE;
	}
	
	public function get_student_with_bday()
	{
		$sql = 'SELECT concat_ws("",p.lastname,", ",p.firstname," ",p.middlename) as fullname,
					   p.gender,
					   p.dob,
					   l.level_desc,
					   date_format(p.dob,"%M %d, %Y") as dob_format
				FROM enrollments e
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				LEFT JOIN levels l ON e.e_grade_level = l.level_id
				WHERE e.verified = 1
				AND e.registrar_approved = 1
				AND e.e_archived = 0
				AND e.e_ay_id = ?
				ORDER BY e.e_grade_level, p.gender, p.lastname, p.firstname, p.middlename';

		$res = $this->db->query($sql,[$this->c_sy->id]);
		return $res->num_rows() >=1 ? $res->result() : FALSE;
	}
}
?>
